开票

SELECT    o.ad_client_id, 
          o.ad_org_id, 
          o.c_bpartner_id, 
          o.c_order_id, 
          o.documentno, 
          o.dateordered, 
          o.c_doctype_id, 
          SUM((l.qtyordered - l.qtyinvoiced) * l.priceactual) AS totallines 
FROM      c_order o 
join      c_orderline l        ON   o.c_order_id = l.c_order_id 
join      c_bpartner bp        ON   o.c_bpartner_id = bp.c_bpartner_id 
left join c_invoiceschedule si ON   bp.c_invoiceschedule_id = si.c_invoiceschedule_id 
WHERE     o.ad_client_id = 1000007 
AND ( /*订单状态=完成、关闭、进行中*/
        o.docstatus = ANY (array['CO', 'CL', 'IP'])) 
AND ( /*销售订单单据类型不等于“报价方案”“报价单”“POS订单”*/
        o.c_doctype_id IN ( 
                            SELECT c_doctype.c_doctype_id 
                            FROM   c_doctype 
                            WHERE  c_doctype.docbasetype = 'SOO' 
                            AND ( 
                                    c_doctype.docsubtypeso <> ALL (array['ON', 'OB', 'WR'])))
    )
          /*销售数量 不等于 开票数量 */
AND     l.qtyordered <> l.qtyinvoiced 
AND ( /*订单的开票规则= 立即开票*/
        o.invoicerule = 'I' 
      /*订单的开票规则= 按订单开票,且订单行的发货数量等于销售数量 (订单发完货再开票)*/
    OR  o.invoicerule = 'O' 
    AND NOT ( 

            EXISTS ( 
                    SELECT 1 
                    FROM   c_orderline zz1 
                    WHERE  zz1.c_order_id = o.c_order_id 
                    AND    zz1.qtyordered <> zz1.qtydelivered
                    )
            ) 
      /*订单的开票规则= 按发货开票,且订单行的发货数量不等于开票数量 */
    OR  o.invoicerule = 'D' AND  l.qtyinvoiced <> l.qtydelivered 
      /*订单的开票规则= 按规则开票,且业务伙伴的开票规则为空 */
    OR  o.invoicerule = 'S' AND  bp.c_invoiceschedule_id IS NULL 
      /*订单的开票规则= 按规则开票,且业务伙伴的开票规则为非空 */
    OR  o.invoicerule = 'S' AND  bp.c_invoiceschedule_id IS NOT NULL 
        AND ( 
      /*开票规则的频度为空、或者为“天”、或者为“周” */
                 si.invoicefrequency IS NULL 
            OR   si.invoicefrequency = 'D' 
            OR   si.invoicefrequency = 'W' 
      /*开票规则的频度为“半月” */
            OR   si.invoicefrequency = 'T' 
            AND ( 
      /*订单的销售日期 <= 当前月+截止日 并且 开票的当前日期 >= 订单月+开票日 */
                     trunc(o.dateordered) <= (firstof(getdate(), 'MM') + si.invoicedaycutoff - 1)
                AND  trunc(getdate()) >= (firstof(o.dateordered, 'MM') + si.invoiceday - 1)
      /* 或者 订单的销售日期 <= 当前月+截止日+14 并且 开票的当前日期 >= 订单月+开票日+14 */
                OR   trunc(o.dateordered) <= (firstof(getdate(), 'MM') + si.invoicedaycutoff + 14)
                AND  trunc(getdate()) >= (firstof(o.dateordered, 'MM') + si.invoiceday + 14 )
                )
      /*开票规则的频度为“每月” */
            OR   si.invoicefrequency = 'M'
      /*订单的销售日期 <= 当前月+截止日 并且 开票的当前日期 >= 订单月+开票日 */
            AND  trunc(o.dateordered) <= (firstof(getdate(), 'MM') + si.invoicedaycutoff - 1)
            AND  trunc(getdate()) >= (firstof(o.dateordered, 'MM') + si.invoiceday - 1)
      /*按开票规则开票,筛选到目标订单后,按订单开票(按销售数量全开) */
            )
      /*只改这里,就可以将“销售订单日期”为“发货单日期”,符合结算习惯 */
      /* 一个销售订单可能有多个发货单,有的发货单符合开票规则,有的发货单不符合
          那么该订单还是应该被筛选出开票,只对符合时间要求的发货单进行开票 */
    )
GROUP BY  o.ad_client_id, 
          o.ad_org_id, 
          o.c_bpartner_id, 
          o.c_order_id, 
          o.documentno, 
          o.dateordered, 
          o.c_doctype_id;
	/**
         * InvoiceGenerate	InvoiceGenerate.java 
         * (org.adempiere.base.process\src\org\compiere\process)
         * L200
         *
	 * 	Generate Shipments   (备注有错,应该是 Invoice)
	 * 	@param pstmt order query 
	 *	@return info
	 */
	private String generate (PreparedStatement pstmt)
	{
		ResultSet rs = null;
		try
		{
			rs = pstmt.executeQuery ();
			while (rs.next ())
			{
				p_MinimumAmtInvSched = null;
				MOrder order = new MOrder (getCtx(), rs, get_TrxName());
				StringBuilder msgsup = new StringBuilder(Msg.getMsg(getCtx(), "Processing")).append(" ").append(order.getDocumentInfo());
				statusUpdate(msgsup.toString());
				
				//	New Invoice Location
				if (!p_ConsolidateDocument 
					|| (m_invoice != null 
					&& m_invoice.getC_BPartner_Location_ID() != order.getBill_Location_ID()) )
					completeInvoice();
				boolean completeOrder = MOrder.INVOICERULE_AfterOrderDelivered.equals(order.getInvoiceRule());
				
				//	Schedule After Delivery
				boolean doInvoice = false;
				if (MOrder.INVOICERULE_CustomerScheduleAfterDelivery.equals(order.getInvoiceRule()))
				{
					m_bp = new MBPartner (getCtx(), order.getBill_BPartner_ID(), null);
					if (m_bp.getC_InvoiceSchedule_ID() == 0)
					{
						log.warning("BPartner has no Schedule - set to After Delivery");
						order.setInvoiceRule(MOrder.INVOICERULE_AfterDelivery);
						order.saveEx();
					}
					else
					{
						MInvoiceSchedule is = MInvoiceSchedule.get(getCtx(), m_bp.getC_InvoiceSchedule_ID(), get_TrxName());
						if (is.canInvoice(order.getDateOrdered())) {
							if (is.isAmount() && is.getAmt() != null)
							p_MinimumAmtInvSched = is.getAmt();
							doInvoice = true;
						} else {
							continue;
						}
					}
				}	//	Schedule
				
				//	After Delivery
				if (doInvoice || MOrder.INVOICERULE_AfterDelivery.equals(order.getInvoiceRule()))
				{
					MInOut[] shipments = order.getShipments();
					for (int i = 0; i < shipments.length; i++)
					{
						MInOut ship = shipments[i];
						if (!ship.isComplete()		//	ignore incomplete or reversals 
							|| ship.getDocStatus().equals(MInOut.DOCSTATUS_Reversed))
							continue;
						MInOutLine[] shipLines = ship.getLines(false);
						for (int j = 0; j < shipLines.length; j++)
						{
							MInOutLine shipLine = shipLines[j];
							if (!order.isOrderLine(shipLine.getC_OrderLine_ID()))
								continue;
							if (!shipLine.isInvoiced())
								createLine (order, ship, shipLine);
						}
						m_line += 1000;
					}
				}
				//	After Order Delivered, Immediate
				else
				{
					MOrderLine[] oLines = order.getLines(true, null);
					for (int i = 0; i < oLines.length; i++)
					{
						MOrderLine oLine = oLines[i];
						BigDecimal toInvoice = oLine.getQtyOrdered().subtract(oLine.getQtyInvoiced());
						if (toInvoice.compareTo(Env.ZERO) == 0 && oLine.getM_Product_ID() != 0)
							continue;
						@SuppressWarnings("unused")
						BigDecimal notInvoicedShipment = oLine.getQtyDelivered().subtract(oLine.getQtyInvoiced());
						//
						boolean fullyDelivered = oLine.getQtyOrdered().compareTo(oLine.getQtyDelivered()) == 0;
					
						//	Complete Order
						if (completeOrder && !fullyDelivered)
						{
							if (log.isLoggable(Level.FINE)) log.fine("Failed CompleteOrder - " + oLine);
							addBufferLog(0, null, null,"Failed CompleteOrder - " + oLine,oLine.get_Table_ID(),oLine.getC_OrderLine_ID()); // Elaine 2008/11/25
							completeOrder = false;
							break;
						}
						//	Immediate
						else if (MOrder.INVOICERULE_Immediate.equals(order.getInvoiceRule()))
						{
							if (log.isLoggable(Level.FINE)) log.fine("Immediate - ToInvoice=" + toInvoice + " - " + oLine);
							BigDecimal qtyEntered = toInvoice;
							//	Correct UOM for QtyEntered
							if (oLine.getQtyEntered().compareTo(oLine.getQtyOrdered()) != 0)
								qtyEntered = toInvoice
									.multiply(oLine.getQtyEntered())
									.divide(oLine.getQtyOrdered(), 12, BigDecimal.ROUND_HALF_UP);
							createLine (order, oLine, toInvoice, qtyEntered);
						}
						else if (!completeOrder)
						{
							if (log.isLoggable(Level.FINE)) log.fine("Failed: " + order.getInvoiceRule() 
								+ " - ToInvoice=" + toInvoice + " - " + oLine);
							addBufferLog(0, null, null,"Failed: " + order.getInvoiceRule() 
								+ " - ToInvoice=" + toInvoice + " - " + oLine,oLine.get_Table_ID(),oLine.getC_OrderLine_ID());
						}
					}	//	for all order lines
					if (MOrder.INVOICERULE_Immediate.equals(order.getInvoiceRule()))
						m_line += 1000;
				}
				
				//	Complete Order successful
				if (completeOrder && MOrder.INVOICERULE_AfterOrderDelivered.equals(order.getInvoiceRule()))
				{
					MInOut[] shipments = order.getShipments();
					for (int i = 0; i < shipments.length; i++)
					{
						MInOut ship = shipments[i];
						if (!ship.isComplete()		//	ignore incomplete or reversals 
							|| ship.getDocStatus().equals(MInOut.DOCSTATUS_Reversed))
							continue;
						MInOutLine[] shipLines = ship.getLines(false);
						for (int j = 0; j < shipLines.length; j++)
						{
							MInOutLine shipLine = shipLines[j];
							if (!order.isOrderLine(shipLine.getC_OrderLine_ID()))
								continue;
							if (!shipLine.isInvoiced())
								createLine (order, ship, shipLine);
						}
						m_line += 1000;
					}
				}	//	complete Order
			}	//	for all orders
		}
		catch (Exception e)
		{
			throw new AdempiereException(e);
		}
		finally
		{
			DB.close(rs, pstmt);
			rs = null;
			pstmt = null;
		}
		completeInvoice();
		StringBuilder msgreturn = new StringBuilder("@Created@ = ").append(m_created);
		return msgreturn.toString();
	}	//	generate